#include "formgreport.h"
#include "ui_formgreport.h"

FormGreport::FormGreport(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::FormGreport)
{
    ui->setupUi(this);
    this->show();
    this->setWindowTitle("通用报表程序");
    Start();
    RefTree();
    RefXMTree();
}

FormGreport::~FormGreport()
{
    delete ui;
}
void FormGreport::Start(){
    QString  str;
    str = QDate::currentDate().toString("yyyy-01-01");
    ui->dtSRQ->setDate(QDate::fromString(str,"yyyy-MM-dd"));
    ui->dtERQ->setDate(QDate::currentDate());
    QString SQL="";
    QString ND=mySQL::ND;
    QSqlQuery query(mySQL::cn); //查询 表并输出，测试能否正常操作数据库

    SQL="";
    SQL.append("SELECT nd FROM  nd ");
    SQL.append(" where ");
    SQL.append(" type_id='");
    SQL.append(mySQL::TypeId);
    SQL.append("'  order by nd desc ");
    query.exec(SQL);
    ui->cmbND->clear();
    ui->cmbND->addItem("全部");
    while(query.next()){
        ui->cmbND->addItem(query.value("nd").toString());
    }
    ui->cmbYF->clear();
    ui->cmbYF->addItem("全部");
    for (int i=1;i<12;i++){
        ui->cmbYF->addItem(QString("%1").arg(i,2,10,QChar('0'))); //变量，2位，十进制，替代空字符 如果10 变成2 就变成2进制的了
     }

}

void FormGreport::RefTree(){
    ui->treeWidget->clear();
    ui->treeWidget->setHeaderLabels(QStringList() << "报表类型" << "报表名称"  );
    ui->treeWidget->header()->setSectionResizeMode(QHeaderView::ResizeToContents); //QTreeWidget设置根据内容自适应宽度

    QString SQL="";
    QSqlQuery query(mySQL::cn);
    QString str="";
    QTreeWidgetItem * Fitem =new QTreeWidgetItem;
    SQL=QString("SELECT idd,type,name FROM GReport  where type_id='%1' order by type,name").arg(mySQL::TypeId);

    query.exec(SQL);
    while(query.next()){
        QStringList child;
        child.clear();
        child << query.value("type").toString().trimmed();
        child << "";
        QString tmpStr=query.value("type").toString().trimmed();
        if (str!=tmpStr){
            str=tmpStr;
            Fitem=new QTreeWidgetItem(child);
            ui->treeWidget->addTopLevelItem(Fitem);
        }
        child.clear();
        child << "";
        child << query.value("name").toString().trimmed();
        child << query.value("type").toString().trimmed();
        child << query.value("idd").toString().trimmed();
        QTreeWidgetItem * citem =new QTreeWidgetItem(child);
        Fitem->addChild(citem);
    }
    query.clear();
}
void FormGreport::RefXMTree()
{
    ui->treeWidgetXM->clear();
    ui->treeWidgetXM->setHeaderLabels(QStringList() << "项目代码" <<  "项目名称" );
    ui->treeWidgetXM->header()->setSectionResizeMode(QHeaderView::ResizeToContents); //QTreeWidget设置根据内容自适应宽度
    //ui->treeWidget->setStyleSheet("QTreeWidget::item{border-right: 1px solid blue;border-left: 1px solid blue;border-bottom: 1px solid blue;}"); //加表格线
    QString SQL="";
    QStringList child;
    SQL=QString("SELECT xmdm,xmmc,xmjc,xmzj FROM `hsxm` WHERE type_id='%1' and nd='%2' order by xmdm").arg(mySQL::TypeId).arg(mySQL::ND);
    QSqlQuery query(mySQL::cn); //查询 表并输出，测试能否正常操作数据库
    query.exec(SQL);
    while(query.next()){
        QString str=query.value("xmdm").toString().trimmed();
         child.clear();
         child << query.value("xmdm").toString();
         child << query.value("xmmc").toString();
         QTreeWidgetItem * citem =new QTreeWidgetItem(child);
         if (str.length() ==1){
             ui->treeWidgetXM->addTopLevelItem(citem);
         }else{
             //遍历找出父节点
             bool f=false;
             QTreeWidgetItemIterator it(ui->treeWidgetXM);
                 while (*it) {
                      //(*it)->text(0);
                     if ((*it)->text(0)==str.left(str.length()-1)){
                         (*it)->addChild(citem);
                         f=true;
                     }
                     ++it;
                 }
                 if(f==false){
                     //如果减1个字符没照到上级 就减2个字符
                     QTreeWidgetItemIterator it(ui->treeWidgetXM);
                     while (*it) {
                          //(*it)->text(0);
                         if ((*it)->text(0)==str.left(str.length()-2)){
                             (*it)->addChild(citem);
                             f=true;
                         }
                         ++it;
                     }
                 }
                 if(f==false){
                     //如果减2个字符没照到上级 就减3个字符
                     QTreeWidgetItemIterator it(ui->treeWidgetXM);
                     while (*it) {
                          //(*it)->text(0);
                         if ((*it)->text(0)==str.left(str.length()-3)){
                             (*it)->addChild(citem);
                             f=true;
                         }
                         ++it;
                     }
                 }
         }
    }
}
void FormGreport::on_treeWidget_itemClicked(QTreeWidgetItem *item, int column)
{
    QString SQL="";
    QString SSKM="";
    QSqlQuery query(mySQL::cn);
    QString KMXX="";
    int i=column;
    i++;
    QStringList list1;
    list1<<"科目代码"<<"科目名称"<<"项目"<<"期初余额"<<"借方金额"<<"贷方金额"<<"余额"<<"";
    ui->tableWidget->clear();
    ui->tableWidget->setColumnCount(list1.size());
    ui->tableWidget->setHorizontalHeaderLabels(list1);
    //设置列宽
    ui->tableWidget->setColumnWidth(0, 120);
    ui->tableWidget->setColumnWidth(1, 150);
    ui->tableWidget->setColumnWidth(2, 150);
    ui->tableWidget->setColumnWidth(3, 120);
    ui->tableWidget->setColumnWidth(4, 120);
    ui->tableWidget->setColumnWidth(5, 120);
    ui->tableWidget->setColumnWidth(6, 120);
    ui->tableWidget->setColumnWidth(7, 0);
    //ui->tablePZ->horizontalHeader()->setStretchLastSection(true); //设置充满表宽度
    //ui->tableWidget1->verticalHeader()->setResizeMode(QHeaderView::ResizeToContents);
    ui->tableWidget->verticalHeader()->setFixedWidth(48);
    ui->tableWidget->verticalHeader()->setDefaultSectionSize(48); //设置行距
    //ui->tablePZ->horizontalHeader()->resizeSection(0,150); //设置表头第一列的宽度为150
    ui->tableWidget->horizontalHeader()->setFixedHeight(48); //设置表头的高度
    ui->tableWidget->horizontalHeader()->setStyleSheet("QHeaderView::section{background:skyblue;}");
    ui->tableWidget->setStyleSheet("selection-background-color:lightblue;"); //设置选中背景色
    ui->tableWidget->setRowCount(2); //暂时设置成2行
    ui->tableWidget->setEditTriggers(QAbstractItemView::NoEditTriggers); //不可编辑
    ui->tableWidget->setSelectionMode(QAbstractItemView::SingleSelection);//单选
    //ui->tableWidget->horizontalScrollBar()->setEnabled(false);


    if (item->text(2)!=""){
        SQL=QString("SELECT name,title,pageend,XM,kmxx FROM GReport  where type_id='%1'  ").arg(mySQL::TypeId);
        SQL+=QString("and type='%1' and name='%2'").arg(item->text(2)).arg(item->text(1));
        //QMessageBox::warning(this,"警告",SQL);
        query.exec(SQL);
        while(query.next()){
            ui->txtReportName->setText(query.value("name").toString().trimmed());
            ui->txtTitle->setText(query.value("title").toString().trimmed());
            ui->txtPageEnd->setText(query.value("pageend").toString());
            KMXX=query.value("kmxx").toString().trimmed();
        }
        if (KMXX!=""){
            //科目信息不为空开始列表格 ，组SQL语句
            QStringList strList = KMXX.split("\n");
            SSKM=" (1=2 ";

            for(int i = 0; i < strList.size(); i++) {
                //取出每一行
                if (strList[i].trimmed()!=""){
                    QStringList strDetail = strList[i].split("|");
                    if (i>ui->tableWidget->rowCount()-1){
                        int row = ui->tableWidget->rowCount();
                        ui->tableWidget->insertRow(row);
                    }
                    QString str="";
                    str=QString(",%1,").arg(strDetail[0]);
                    ui->tableWidget->setItem(i,0,new QTableWidgetItem(str));
                    ui->tableWidget->setItem(i,1,new QTableWidgetItem(strDetail[1]));
                    ui->tableWidget->setItem(i,2,new QTableWidgetItem(strDetail[2]));
                    ui->tableWidget->setItem(i,3,new QTableWidgetItem("0.00"));
                    ui->tableWidget->setItem(i,4,new QTableWidgetItem("0.00"));
                    ui->tableWidget->setItem(i,5,new QTableWidgetItem("0.00"));
                    ui->tableWidget->setItem(i,6,new QTableWidgetItem("0.00"));
                    ui->tableWidget->setItem(i,7,new QTableWidgetItem(strDetail[3]));
                    ui->tableWidget->item(i,0)->setTextAlignment(Qt::AlignLeft | Qt::AlignVCenter);
                    ui->tableWidget->item(i,1)->setTextAlignment(Qt::AlignLeft | Qt::AlignVCenter);
                    ui->tableWidget->item(i,2)->setTextAlignment(Qt::AlignLeft | Qt::AlignVCenter);
                    ui->tableWidget->item(i,3)->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
                    ui->tableWidget->item(i,4)->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
                    ui->tableWidget->item(i,5)->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
                    ui->tableWidget->item(i,6)->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
                    ui->tableWidget->item(i,7)->setTextAlignment(Qt::AlignCenter | Qt::AlignVCenter);
                    SSKM+=QString(" or kmdm='%1' ").arg(strDetail[0].trimmed().replace(",",""));
                }


            }
            SSKM+=") ";
            //先列出期初数
            SQL=QString("SELECT kmdm,xmbh,SUM(CASE WHEN jd='借' THEN je ELSE 0 END)  AS JF,SUM(CASE WHEN jd='贷' THEN je ELSE 0 END)  AS DF FROM `pz` WHERE type_id='%1'  ").arg(mySQL::TypeId);
            SQL+=QString(" and  date_format(rq,'%Y-%m-%d') <'%1' ").arg(ui->dtSRQ->date().toString("yyyy-MM-dd"));
            SQL+=QString(" and %1").arg(SSKM);

            SQL+=" group by kmdm,xmbh order by kmdm,xmbh ";
            //qDebug() <<SQL;
            query.exec(SQL);
            while(query.next()){
                QString tmpKM=QString(",%1,").arg(query.value("kmdm").toString().trimmed());
                QString tmpXM=query.value("xmbh").toString().trimmed();
                //循环将数据填入表中
                for(int i=0;i<ui->tableWidget->rowCount();i++){
                    if(ui->tableWidget->item(i,0)!=NULL && ui->tableWidget->item(i,0)->text().trimmed()!="" ){
                            QString KM=ui->tableWidget->item(i,0)->text().trimmed();
                            if (KM.contains(tmpKM,Qt::CaseSensitive)) { //如果KM里包含tmpKM
                                //if (KM.left(tmpKM.length())==tmpKM) {
                                double QCJE=ui->tableWidget->item(i,3)->text().toDouble();
                                double YE=0;
                                if (ui->tableWidget->item(i,2)->text().trimmed()==""){
                                    if(ui->tableWidget->item(i,7)->text().trimmed()=="借方"){
                                        YE=QCJE+query.value("JF").toDouble()-query.value("DF").toDouble();
                                    }else{
                                        YE=QCJE-query.value("JF").toDouble()+query.value("DF").toDouble();
                                    }
                                    ui->tableWidget->item(i,3)->setText(QString::number(YE, 'f', 2) );
                                }else{
                                    QString XM=ui->tableWidget->item(i,2)->text().trimmed();
                                    if (XM.left(tmpXM.length())==tmpXM) {
                                        if(ui->tableWidget->item(i,7)->text().trimmed()=="借方"){
                                            YE=QCJE+query.value("JF").toDouble()-query.value("DF").toDouble();
                                        }else{
                                            YE=QCJE-query.value("JF").toDouble()+query.value("DF").toDouble();
                                        }
                                        ui->tableWidget->item(i,3)->setText(QString::number(YE, 'f', 2) );
                                    }
                                }
                            }
                    }
                }

            }
            //下面开始获取本期发生：
            SQL=QString("SELECT kmdm,xmbh,SUM(CASE WHEN jd='借' THEN je ELSE 0 END)  AS JF,SUM(CASE WHEN jd='贷' THEN je ELSE 0 END)  AS DF FROM `pz` WHERE type_id='%1'  ").arg(mySQL::TypeId);
            SQL+=QString(" and  date_format(rq,'%Y-%m-%d') >='%1' ").arg(ui->dtSRQ->date().toString("yyyy-MM-dd"));
            SQL+=QString(" and  date_format(rq,'%Y-%m-%d') <='%1' ").arg(ui->dtERQ->date().toString("yyyy-MM-dd"));

            SQL+=QString(" and %1").arg(SSKM);

            SQL+=" group by kmdm,xmbh order by kmdm,xmbh ";
            //qDebug() <<SQL;
            query.exec(SQL);
            while(query.next()){
                QString tmpKM=query.value("kmdm").toString().trimmed();
                QString tmpXM=query.value("xmbh").toString().trimmed();
                //循环将数据填入表中
                for(int i=0;i<ui->tableWidget->rowCount();i++){
                    if(ui->tableWidget->item(i,0)!=NULL && ui->tableWidget->item(i,0)->text().trimmed()!="" ){
                            QString KM=ui->tableWidget->item(i,0)->text().trimmed();
                            if (KM.contains(tmpKM,Qt::CaseSensitive)) { //如果KM里包含tmpKM
                                double QCJE=ui->tableWidget->item(i,3)->text().toDouble();
                                double JF=ui->tableWidget->item(i,4)->text().toDouble();
                                double DF=ui->tableWidget->item(i,5)->text().toDouble();
                                double YE=0;
                                if (ui->tableWidget->item(i,2)->text().trimmed()==""){
                                    JF+=query.value("JF").toDouble();
                                    DF+=query.value("DF").toDouble();

                                    if(ui->tableWidget->item(i,7)->text().trimmed()=="借方"){
                                        YE=QCJE+JF-DF;
                                    }else{
                                        YE=QCJE-JF+DF;
                                    }
                                    ui->tableWidget->item(i,4)->setText(QString::number(JF, 'f', 2) );
                                    ui->tableWidget->item(i,5)->setText(QString::number(DF, 'f', 2) );
                                    ui->tableWidget->item(i,6)->setText(QString::number(YE, 'f', 2) );
                                }else{
                                    QString XM=ui->tableWidget->item(i,2)->text().trimmed();

                                    if (XM==tmpXM) {
                                        JF+=query.value("JF").toDouble();
                                        DF+=query.value("DF").toDouble();
                                        if(ui->tableWidget->item(i,7)->text().trimmed()=="借方"){
                                            YE=QCJE+JF-DF;
                                        }else{
                                            YE=QCJE-JF+DF;
                                        }
                                        ui->tableWidget->item(i,4)->setText(QString::number(JF, 'f', 2) );
                                        ui->tableWidget->item(i,5)->setText(QString::number(DF, 'f', 2) );
                                        ui->tableWidget->item(i,6)->setText(QString::number(YE, 'f', 2) );
                                    }
                                }
                            }
                    }
                }
            }
            //处理以下数据。
            for(int i=0;i<ui->tableWidget->rowCount();i++){
                if(ui->tableWidget->item(i,0)!=NULL && ui->tableWidget->item(i,0)->text().trimmed()!="" ){
                    QString str=ui->tableWidget->item(i,0)->text().trimmed();
                    if (str.left(1)==","){
                        str=str.mid(1);
                        ui->tableWidget->item(i,0)->setText(str);
                    }
                    if (str.right(1)==","){
                        str=str.left(str.length()-1);
                        ui->tableWidget->item(i,0)->setText(str);
                    }
                    if (str.right(1)==","){
                        str=str.left(str.length()-1);
                        ui->tableWidget->item(i,0)->setText(str);
                    }

                    str = QString("%L1").arg(ui->tableWidget->item(i,3)->text().toFloat(), 3, 'f', 2);
                    ui->tableWidget->item(i,3)->setText(str);
                    str = QString("%L1").arg(ui->tableWidget->item(i,4)->text().toFloat(), 3, 'f', 2);
                    ui->tableWidget->item(i,4)->setText(str);
                    str = QString("%L1").arg(ui->tableWidget->item(i,5)->text().toFloat(), 3, 'f', 2);
                    ui->tableWidget->item(i,5)->setText(str);
                    str = QString("%L1").arg(ui->tableWidget->item(i,6)->text().toFloat(), 3, 'f', 2);
                    ui->tableWidget->item(i,6)->setText(str);


                }
            }
            QTreeWidgetItemIterator it(ui->treeWidgetXM);
            while (*it) {
                 //(*it)->text(0);
                for(int i=0;i<ui->tableWidget->rowCount();i++){
                    if(ui->tableWidget->item(i,2)!=NULL && ui->tableWidget->item(i,2)->text().trimmed()!="" ){
                        QString str=QString("%1,").arg(ui->tableWidget->item(i,2)->text().trimmed());
                        str=str.replace(QString("%1,").arg((*it)->text(0)),QString("%1-%2,").arg((*it)->text(0)).arg((*it)->text(1)));
                        ui->tableWidget->item(i,2)->setText(str);
                    }
                }
                ++it;
            }
            for(int i=0;i<ui->tableWidget->rowCount();i++){
                if(ui->tableWidget->item(i,2)!=NULL && ui->tableWidget->item(i,2)->text().trimmed()!="" ){
                    QString str=QString("%1").arg(ui->tableWidget->item(i,2)->text().trimmed());
                    str=str.replace(",","");
                    ui->tableWidget->item(i,2)->setText(str);
                }
            }
        }
    }
 }


void FormGreport::on_cmdCopy_clicked()
{
    JCode::FZTableWidget(ui->tableWidget);
}

void FormGreport::on_cmdPrint_clicked()
{
    QString fileName="";
    fileName= QCoreApplication::applicationDirPath();
    fileName +="/tmp.pdf";
    JCode::TableWidgetTOPDF(ui->tableWidget,fileName,ui->txtReportName->text(),ui->txtTitle->text(),ui->txtPageEnd->text(),"纵向");

}
